create or replace
PACKAGE BODY PA_RESERVATION AS

	current_reservation  view_reservation%ROWTYPE;

	function getReservationsClient(user_id in number) return sqlcur AS
		cur sqlcur;
	BEGIN
    
		OPEN cur FOR
            SELECT
                  *
            FROM 
                  VIEW_RESERVATION
            WHERE
                  num_utilisateur = user_id
            ORDER BY 
                  DATE_RESERVATION desc;
        RETURN cur;
    
		RETURN NULL;
	END GetReservationsClient;
	
	
	FUNCTION Get(p_num_reservation in number) return view_reservation%ROWTYPE IS
		CURSOR c_reservation(numreservation in number)
		IS
			SELECT
				*
			FROM
				view_reservation
			WHERE
                  num_reservation = numreservation;
	BEGIN
		OPEN c_reservation(p_num_reservation);
		FETCH c_reservation INTO current_reservation;

		IF c_reservation%NOTFOUND THEN
			RAISE NO_DATA_FOUND;
		END IF;

		CLOSE c_reservation;
		RETURN current_reservation;
	END;

	function getLignesReservation(p_num_reservation number, user_id in number) return sqlcur AS
		cur sqlcur;
	BEGIN
    
		OPEN cur FOR
            SELECT
                  *
            FROM 
                  VIEW_LIGNE_RESERVATION
            WHERE
				num_utilisateur = user_id
            AND   
				num_reservation = p_num_reservation
            ORDER BY 
                  num_ligne;
        RETURN cur;
    
		RETURN NULL;
	END getLignesReservation;
  
	function getPlacesLibres(p_num_spectacle in number, p_num_repres in number, p_num_cat_place in number, p_nb_places in number) return sqlcur AS
		cur sqlcur;
	BEGIN
    
		OPEN cur FOR
			SELECT
				*
			FROM
			(
				SELECT
					*
				FROM 
					  VIEW_PLACES_LIBRES
				WHERE
					num_spectacle = p_num_spectacle
				AND
					num_repres = p_num_repres
				AND
					num_cat_place = p_num_cat_place
			)
			where rownum <= p_nb_places;
			
        RETURN cur;
    
		RETURN NULL;
	END getPlacesLibres;
  
	PROCEDURE Add
	(	  
		  p_num_spectacle in number
		, p_num_repres in number
		, p_num_salle in number
		, p_num_utilisateur in number
		, array_reservation pa_representation.n_array_array
		, p_type_retrait in number
	) 
	AS
		list_places sqlcur;
		curr_num_reservation number;
		l_cat_num number;
		l_nb_places number;
		l_num_ligne number := 1;
		l_prix number;
		rec_place VIEW_PLACES_LIBRES%rowtype;
	BEGIN
		
		insert into reservation (num_reservation, num_typeretrait, num_utilisateur, date_reservation)
		values (seq_reservation.nextval, p_type_retrait, p_num_utilisateur, sysdate);
		
		select seq_reservation.currval into curr_num_reservation from dual;
		
		commit;
		
 		for i in 1..array_reservation.count loop
		
			l_cat_num := array_reservation(i)(1);
			l_nb_places := array_reservation(i)(2);
			
			if (l_nb_places > 0) then
				
				list_places := getPlacesLibres(p_num_spectacle, p_num_repres, l_cat_num, l_nb_places);
				fetch list_places into rec_place;
				
				
  				while(list_places%found)
				loop
					if (rec_place.tarif_remise is not null) then
						l_prix := rec_place.tarif_remise;
					else
						l_prix := rec_place.tarif_base;
					end if;
					
					insert into ligne_reservation (num_reservation, num_ligne, num_salle, num_place, num_repres, num_spectacle, date_reservation, prix_achat)
					values (curr_num_reservation, l_num_ligne, p_num_salle, rec_place.num_place, p_num_repres, p_num_spectacle, sysdate, l_prix);
					
					l_num_ligne := l_num_ligne +1;
					fetch list_places into rec_place;
				end loop; 
				
				commit;
			
			end if;
			
		end loop;
		
	END;

END PA_RESERVATION;
/